package com.zfzs.post.modules.transaction.repository;

import com.zfzs.post.modules.transaction.domain.TransactionInfo;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 终端交易
 *
 * @author ck
 * @date 2019-10-08
 */
public interface TransactionInfoRepository extends JpaRepository<TransactionInfo, Long>, JpaSpecificationExecutor {

    //根据时间、代理商查询商户交易总额
    @Query(value = "SELECT " +
            "IFNULL(sum(t.amount), 0.00) " +
            "FROM " +
            "transaction_info t " +
            "LEFT JOIN merchant m ON m.sn = t.sn " +
            "WHERE " +
            "m.agent_id = ?1 " +
            "AND m.create_date >= ?2 " +
            "AND m.create_date <= ?3", nativeQuery = true)
    BigDecimal getTransactionSumByDate(Long id, String startDate, String endDate);
    //根据时间、代理商查询商户交易数量
    @Query(value = "SELECT " +
            "count(distinct t.sn) " +
            "FROM " +
            "transaction_info t " +
            "LEFT JOIN merchant m ON m.sn = t.sn " +
            "WHERE " +
            "m.agent_id = ?1 " +
            "AND m.create_date >= ?2 " +
            "AND m.create_date <= ?3", nativeQuery = true)
    int getTransactionCountByDate(Long id, String startDate, String endDate);

    //根据时间、代理商查询商户交易总额
    @Query(value = "SELECT IFNULL(SUM(amount), 0.00) FROM transaction_info WHERE channel_id = ?1 AND agent_id in (?2) AND transaction_time >= ?3 AND transaction_time <= ?4", nativeQuery = true)
    BigDecimal querySumByAgentIds(Long channelId,List<Long> ids, String startDate, String endDate);

    //根据时间、代理商查询商户交易总额
    @Query(value = "select(SELECT IFNULL(SUM(amount), 0.00) FROM transaction_info WHERE channel_id = ?1 AND agent_id in (?2) AND transaction_time >= ?3 AND transaction_time <= ?4)as flowSum," +
            "(SELECT COUNT(transaction_id) FROM transaction_info WHERE channel_id = ?1 AND agent_id in (?2) AND transaction_time >= ?3 AND transaction_time <= ?4)as transactionCount," +
            "(SELECT COUNT(id) FROM post_terminal_info WHERE channel_id = ?1 AND agent_id in (?2) AND activation_date >= ?3 AND activation_date <= ?4)as activationCount," +
            "(SELECT COUNT(id) FROM merchant WHERE agent_id in (?2) AND create_date >= ?3 AND create_date <= ?4)as merchantCount", nativeQuery = true)
    Map<String,Object> queryTransactionSum(Long channelId, List<Long> ids, String startDate, String endDate);

    @Query(value = "select * from transaction_info where transaction_time=?1 and channel_id =?2 and is_benefited=0",nativeQuery = true)
    List<TransactionInfo> findByDateAndChannelId( String tranDate, Long channelId);
}